﻿using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System;
using VOL.Core.DBManager;
using System.Linq;
using System.Collections;

namespace VOL.WebApi.Controllers.BigScreen
{
    [Route("api/digitaldata")]
    [ApiController]
    public class DigitalData : Controller
    {
        //获取今年调查表的所有评价数的等级划分
        [HttpPost, HttpGet, Route("getItSurveyInfo"), AllowAnonymous]
        public object getItSurveyInfo(string month)
        {
            Dictionary<String, object> dic = new Dictionary<String, object>();
            try
            {
                string tsql = "";
                if (month == "本月" || month == null)
                {
                    tsql = "SELECT * FROM ( SELECT  SUM(case when q1 = 'A' then 1 else 0 end)+ sum(case when q2 = 'A' then 1 else 0 end )+ sum(case when q3 = 'A' then 1 else 0 end )+ sum(case when q4 = 'A' then 1 else 0 end )+ sum(case when q5 = 'A' then 1 else 0 end )+ sum(case when q6 = 'A' then 1 else 0 end )+ sum(case when q7 = 'A' then 1 else 0 end )+ sum(case when q8 = 'A' then 1 else 0 end )+ sum(case when q9 = 'A' then 1 else 0 end )+ sum(case when q10 = 'A' then 1 else 0 end )  AS 'A',  SUM(case when q1 = 'B' then 1 else 0 end)+ sum(case when q2 = 'B' then 1 else 0 end )+ sum(case when q3 = 'B' then 1 else 0 end )+ sum(case when q4 = 'B' then 1 else 0 end )+ sum(case when q5 = 'B' then 1 else 0 end )+ sum(case when q6 = 'B' then 1 else 0 end )+ sum(case when q7 = 'B' then 1 else 0 end )+ sum(case when q8 = 'B' then 1 else 0 end )+ sum(case when q9 = 'B' then 1 else 0 end )+ sum(case when q10 = 'B' then 1 else 0 end )  AS 'B',  SUM(case when q1 = 'C' then 1 else 0 end)+ sum(case when q2 = 'C' then 1 else 0 end )+ sum(case when q3 = 'C' then 1 else 0 end )+ sum(case when q4 = 'C' then 1 else 0 end )+ sum(case when q5 = 'C' then 1 else 0 end )+ sum(case when q6 = 'C' then 1 else 0 end )+ sum(case when q7 = 'C' then 1 else 0 end )+ sum(case when q8 = 'C' then 1 else 0 end )+ sum(case when q9 = 'C' then 1 else 0 end )+ sum(case when q10 = 'C' then 1 else 0 end )  AS 'C',  SUM(case when q1 = 'D' then 1 else 0 end)+ sum(case when q2 = 'D' then 1 else 0 end )+ sum(case when q3 = 'D' then 1 else 0 end )+ sum(case when q4 = 'D' then 1 else 0 end )+ sum(case when q5 = 'D' then 1 else 0 end )+ sum(case when q6 = 'D' then 1 else 0 end )+ sum(case when q7 = 'D' then 1 else 0 end )+ sum(case when q8 = 'D' then 1 else 0 end )+ sum(case when q9 = 'D' then 1 else 0 end )+ sum(case when q10 = 'D' then 1 else 0 end )  AS 'D',  SUM(case when q1 = 'E' then 1 else 0 end)+ sum(case when q2 = 'E' then 1 else 0 end )+ sum(case when q3 = 'E' then 1 else 0 end )+ sum(case when q4 = 'E' then 1 else 0 end )+ sum(case when q5 = 'E' then 1 else 0 end )+ sum(case when q6 = 'E' then 1 else 0 end )+ sum(case when q7 = 'E' then 1 else 0 end )+ sum(case when q8 = 'E' then 1 else 0 end )+ sum(case when q9 = 'E' then 1 else 0 end )+ sum(case when q10 = 'E' then 1 else 0 end )  AS 'E',  SUM(case when q1 = 'F' then 1 else 0 end)+ sum(case when q2 = 'F' then 1 else 0 end )+ sum(case when q3 = 'F' then 1 else 0 end )+ sum(case when q4 = 'F' then 1 else 0 end )+ sum(case when q5 = 'F' then 1 else 0 end )+ sum(case when q6 = 'F' then 1 else 0 end )+ sum(case when q7 = 'F' then 1 else 0 end )+ sum(case when q8 = 'F' then 1 else 0 end )+ sum(case when q9 = 'F' then 1 else 0 end )+ sum(case when q10 = 'F' then 1 else 0 end )  AS 'F'  FROM it_survey WHERE month(CreateDate) = month(GETDATE()) and YEAR(CreateDate) = YEAR(GETDATE())) as a";
                }
                else if (month == "全年")
                {
                    tsql = "SELECT * FROM ( SELECT  SUM(case when q1 = 'A' then 1 else 0 end)+ sum(case when q2 = 'A' then 1 else 0 end )+ sum(case when q3 = 'A' then 1 else 0 end )+ sum(case when q4 = 'A' then 1 else 0 end )+ sum(case when q5 = 'A' then 1 else 0 end )+ sum(case when q6 = 'A' then 1 else 0 end )+ sum(case when q7 = 'A' then 1 else 0 end )+ sum(case when q8 = 'A' then 1 else 0 end )+ sum(case when q9 = 'A' then 1 else 0 end )+ sum(case when q10 = 'A' then 1 else 0 end )  AS 'A',  SUM(case when q1 = 'B' then 1 else 0 end)+ sum(case when q2 = 'B' then 1 else 0 end )+ sum(case when q3 = 'B' then 1 else 0 end )+ sum(case when q4 = 'B' then 1 else 0 end )+ sum(case when q5 = 'B' then 1 else 0 end )+ sum(case when q6 = 'B' then 1 else 0 end )+ sum(case when q7 = 'B' then 1 else 0 end )+ sum(case when q8 = 'B' then 1 else 0 end )+ sum(case when q9 = 'B' then 1 else 0 end )+ sum(case when q10 = 'B' then 1 else 0 end )  AS 'B',  SUM(case when q1 = 'C' then 1 else 0 end)+ sum(case when q2 = 'C' then 1 else 0 end )+ sum(case when q3 = 'C' then 1 else 0 end )+ sum(case when q4 = 'C' then 1 else 0 end )+ sum(case when q5 = 'C' then 1 else 0 end )+ sum(case when q6 = 'C' then 1 else 0 end )+ sum(case when q7 = 'C' then 1 else 0 end )+ sum(case when q8 = 'C' then 1 else 0 end )+ sum(case when q9 = 'C' then 1 else 0 end )+ sum(case when q10 = 'C' then 1 else 0 end )  AS 'C',  SUM(case when q1 = 'D' then 1 else 0 end)+ sum(case when q2 = 'D' then 1 else 0 end )+ sum(case when q3 = 'D' then 1 else 0 end )+ sum(case when q4 = 'D' then 1 else 0 end )+ sum(case when q5 = 'D' then 1 else 0 end )+ sum(case when q6 = 'D' then 1 else 0 end )+ sum(case when q7 = 'D' then 1 else 0 end )+ sum(case when q8 = 'D' then 1 else 0 end )+ sum(case when q9 = 'D' then 1 else 0 end )+ sum(case when q10 = 'D' then 1 else 0 end )  AS 'D',  SUM(case when q1 = 'E' then 1 else 0 end)+ sum(case when q2 = 'E' then 1 else 0 end )+ sum(case when q3 = 'E' then 1 else 0 end )+ sum(case when q4 = 'E' then 1 else 0 end )+ sum(case when q5 = 'E' then 1 else 0 end )+ sum(case when q6 = 'E' then 1 else 0 end )+ sum(case when q7 = 'E' then 1 else 0 end )+ sum(case when q8 = 'E' then 1 else 0 end )+ sum(case when q9 = 'E' then 1 else 0 end )+ sum(case when q10 = 'E' then 1 else 0 end )  AS 'E',  SUM(case when q1 = 'F' then 1 else 0 end)+ sum(case when q2 = 'F' then 1 else 0 end )+ sum(case when q3 = 'F' then 1 else 0 end )+ sum(case when q4 = 'F' then 1 else 0 end )+ sum(case when q5 = 'F' then 1 else 0 end )+ sum(case when q6 = 'F' then 1 else 0 end )+ sum(case when q7 = 'F' then 1 else 0 end )+ sum(case when q8 = 'F' then 1 else 0 end )+ sum(case when q9 = 'F' then 1 else 0 end )+ sum(case when q10 = 'F' then 1 else 0 end )  AS 'F'  FROM it_survey WHERE YEAR(CreateDate) = YEAR(GETDATE()) ) as a";
                }
                else
                {
                    tsql = "SELECT * FROM ( SELECT  SUM(case when q1 = 'A' then 1 else 0 end)+ sum(case when q2 = 'A' then 1 else 0 end )+ sum(case when q3 = 'A' then 1 else 0 end )+ sum(case when q4 = 'A' then 1 else 0 end )+ sum(case when q5 = 'A' then 1 else 0 end )+ sum(case when q6 = 'A' then 1 else 0 end )+ sum(case when q7 = 'A' then 1 else 0 end )+ sum(case when q8 = 'A' then 1 else 0 end )+ sum(case when q9 = 'A' then 1 else 0 end )+ sum(case when q10 = 'A' then 1 else 0 end )  AS 'A',  SUM(case when q1 = 'B' then 1 else 0 end)+ sum(case when q2 = 'B' then 1 else 0 end )+ sum(case when q3 = 'B' then 1 else 0 end )+ sum(case when q4 = 'B' then 1 else 0 end )+ sum(case when q5 = 'B' then 1 else 0 end )+ sum(case when q6 = 'B' then 1 else 0 end )+ sum(case when q7 = 'B' then 1 else 0 end )+ sum(case when q8 = 'B' then 1 else 0 end )+ sum(case when q9 = 'B' then 1 else 0 end )+ sum(case when q10 = 'B' then 1 else 0 end )  AS 'B',  SUM(case when q1 = 'C' then 1 else 0 end)+ sum(case when q2 = 'C' then 1 else 0 end )+ sum(case when q3 = 'C' then 1 else 0 end )+ sum(case when q4 = 'C' then 1 else 0 end )+ sum(case when q5 = 'C' then 1 else 0 end )+ sum(case when q6 = 'C' then 1 else 0 end )+ sum(case when q7 = 'C' then 1 else 0 end )+ sum(case when q8 = 'C' then 1 else 0 end )+ sum(case when q9 = 'C' then 1 else 0 end )+ sum(case when q10 = 'C' then 1 else 0 end )  AS 'C',  SUM(case when q1 = 'D' then 1 else 0 end)+ sum(case when q2 = 'D' then 1 else 0 end )+ sum(case when q3 = 'D' then 1 else 0 end )+ sum(case when q4 = 'D' then 1 else 0 end )+ sum(case when q5 = 'D' then 1 else 0 end )+ sum(case when q6 = 'D' then 1 else 0 end )+ sum(case when q7 = 'D' then 1 else 0 end )+ sum(case when q8 = 'D' then 1 else 0 end )+ sum(case when q9 = 'D' then 1 else 0 end )+ sum(case when q10 = 'D' then 1 else 0 end )  AS 'D',  SUM(case when q1 = 'E' then 1 else 0 end)+ sum(case when q2 = 'E' then 1 else 0 end )+ sum(case when q3 = 'E' then 1 else 0 end )+ sum(case when q4 = 'E' then 1 else 0 end )+ sum(case when q5 = 'E' then 1 else 0 end )+ sum(case when q6 = 'E' then 1 else 0 end )+ sum(case when q7 = 'E' then 1 else 0 end )+ sum(case when q8 = 'E' then 1 else 0 end )+ sum(case when q9 = 'E' then 1 else 0 end )+ sum(case when q10 = 'E' then 1 else 0 end )  AS 'E',  SUM(case when q1 = 'F' then 1 else 0 end)+ sum(case when q2 = 'F' then 1 else 0 end )+ sum(case when q3 = 'F' then 1 else 0 end )+ sum(case when q4 = 'F' then 1 else 0 end )+ sum(case when q5 = 'F' then 1 else 0 end )+ sum(case when q6 = 'F' then 1 else 0 end )+ sum(case when q7 = 'F' then 1 else 0 end )+ sum(case when q8 = 'F' then 1 else 0 end )+ sum(case when q9 = 'F' then 1 else 0 end )+ sum(case when q10 = 'F' then 1 else 0 end )  AS 'F'  FROM it_survey WHERE month(CreateDate) = "+month+ " and YEAR(CreateDate) = YEAR(GETDATE())) as a";
                }
                List<dynamic> tt = DBServerProvider.SqlDapper.QueryList<dynamic>(tsql, null).ToList();
                List<object> list2 = new List<object>();
                list2.Add("A");
                list2.Add("B");
                list2.Add("C");
                list2.Add("D");
                list2.Add("E");
                list2.Add("F");
                List<object> list1 = new List<object>();
                foreach (KeyValuePair<string, object> col in tt[0])
                {
                    string aa = col.Key;//属性
                    string bb = col.Value.ToString()??"";//值
                    list1.Add(bb);
                }
                dic.Add("list1", list1);
                dic.Add("list2", list2);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);

            }

            return dic;
        }

        //获取今年的工作任务数据
        [HttpPost, HttpGet, Route("getCcTackInfo"), AllowAnonymous]
        public List<dynamic> getCctaskInfo(string month)
        {
            List<dynamic> list = new List<dynamic>();
            try
            {
                String sql = null;
                //sql = "SELECT CASE WHEN progress IS NULL OR progress = 0 THEN '未开始' WHEN progress > 0 AND progress < 100 THEN '进行中' WHEN progress = 100 THEN '已完成' END as progressDemo, COUNT(*) AS num FROM cc_task WHERE YEAR(rec_datetime) = YEAR(GETDATE()) group by progress";
                if (month == "本月" || month == null)
                {
                    sql = "with progressFlag as (select progress,case when progress is null or progress=0 then '未开始' when progress>0 and progress<100 then '进行中' when progress =100 then '已完成' end as progressDemo from cc_task where month(rec_datetime) = month(GETDATE()) and year(rec_datetime)=year(GETDATE())) select progressDemo, count(*) as num from progressFlag group by progressDemo";
                }
                else if (month == "全年")
                {
                    sql = "with progressFlag as (select progress,case when progress is null or progress=0 then '未开始' when progress>0 and progress<100 then '进行中' when progress =100 then '已完成' end as progressDemo from cc_task where YEAR(rec_datetime) = YEAR(GETDATE())) select progressDemo, count(*) as num from progressFlag group by progressDemo";
                }else
                {
                    sql = "with progressFlag as (select progress,case when progress is null or progress=0 then '未开始' when progress>0 and progress<100 then '进行中' when progress =100 then '已完成' end as progressDemo from cc_task where month(rec_datetime) = "+month+ " and year(rec_datetime)=year(GETDATE())) select progressDemo, count(*) as num from progressFlag group by progressDemo";
                }

                list = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            return list;
        }

        //查找任务表今年未完成的数据
        [HttpPost, HttpGet, Route("getCctaskList"), AllowAnonymous]
        public List<dynamic> getCctaskList(string month)
        {

            List<dynamic> list = new List<dynamic>();
            try
            {
                string sql = null;
                if (month == "本月" || month == null)
                {
                    sql = "SELECT  cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress < 100 AND month(rec_datetime) = month(GETDATE()) and YEAR(CreateDate) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                else if(month == "全年")
                {
                    sql = "SELECT  cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress < 100 AND YEAR(rec_datetime) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                else
                {
                    sql = "SELECT  cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress < 100 AND month(rec_datetime) = "+month+ " and YEAR(CreateDate) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                    
                list = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        //查找任务表今年已完成的数据
        [HttpPost, HttpGet, Route("getCctaskOfFinishList"), AllowAnonymous]
        public List<dynamic> GetCctaskOfFinishList(string month)
        {
            List<dynamic> list = new List<dynamic>();
            try
            {
                string sql = null;
                if (month == "本月" || month == null)
                {
                    sql = "SELECT cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress = 100 AND month(rec_datetime) = month(GETDATE()) and YEAR(CreateDate) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                else if (month == "全年")
                {
                    sql = "SELECT cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress = 100 AND YEAR(rec_datetime) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                else
                {
                    sql = "SELECT cc_task_id , CONVERT(varchar(100),rec_datetime,23) as rec_datetime , tc_departname , tc_ren , zeren , progress , q_decribe ,a_decribe FROM cc_task WHERE progress = 100 AND month(rec_datetime) = "+month+ " and YEAR(CreateDate) = YEAR(GETDATE()) ORDER BY rec_datetime DESC";
                }
                list = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }

        //查找每人的任务量及完成率
        [HttpPost,HttpGet,Route("get_quota_completionRate"),AllowAnonymous]
        public List<dynamic> Get_quota_completionRate(string month)
        {
            DateTime now = DateTime.Now;
            List<dynamic> list = new List<dynamic>();
            try
            {
                List<dynamic> oldList = new List<dynamic>();
                string sql = null;
                if (month == "本月" || month==null)
                {
                    sql = "select count(*) num,sum(progress) rate,zeren from cc_task where month(rec_datetime)=month(getdate()) and year(rec_datetime)=year(GETDATE()) group by zeren";
                }else if (month == "全年")
                {
                    sql = "select count(*) num,sum(progress) rate,zeren from cc_task where year(rec_datetime)=year(getdate()) and year(rec_datetime)=year(GETDATE()) group by zeren";
                }
                else
                {
                    sql = "select count(*) num,sum(progress) rate,zeren from cc_task where month(rec_datetime)="+month+ " and year(rec_datetime)=year(GETDATE()) group by zeren";
                }
                
                oldList = DBServerProvider.SqlDapper.QueryList<dynamic>(sql, null).ToList();
                oldList.ForEach(x =>
                {
                    string[] oldZerens = x.zeren.Split(new char[3] { '，', '、', ' ' });
                    int oldRate = x.rate;
                    int oldNum = x.num;//任务量
                    foreach (string oldZeren in oldZerens)
                    {
                        var matchingItems = list.Where(item => item.zeren == oldZeren);
                        foreach (dynamic item in matchingItems)
                        {
                            item.rate += oldRate;
                            item.num += oldNum;
                        }
                        if (matchingItems.Count() == 0)
                        {
                            dynamic item = x;
                            item.zeren = oldZeren;
                            list.Add(item);
                        }
                    }

                });
                if (list != null)
                {
                    list.ForEach(x => { x.rate = Math.Round(x.rate / (x.num * 1.0), 2); });//完成率
                }
                
                
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
        }
    }
}
